using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class LoaiduocphamDao:IDaoObject<LoaiDuocPhamDto, int>
    {
        #region IDaoObject<LoaiDuocPhamDTO,int> Members

        public LoaiDuocPhamDto SaveOrUpdate(LoaiDuocPhamDto obj)
        {
            return SaveOrUpdate(obj, null);
        }

        public LoaiDuocPhamDto SaveOrUpdate(LoaiDuocPhamDto obj, OleDbTransaction transaction)
        {
            OleDbCommand cmd = new OleDbCommand();
            if (transaction != null)
            {
                cmd.Connection = transaction.Connection;
                cmd.Transaction = transaction;
            }
            else
            {
                cmd.Connection = MyConnection.ConnectionData();
            }
            string sqlPattern = "";
            //string sqlString="";
            int id = 0;
            try
            {
                if (obj.Id > 0 || CheckAlready(obj.TenLoaiDuocPham, out id))
                {
                    //No need to update table 'cause there's no more data field
                    if (obj.Id <= 0) obj.Id = id;
                    return obj;
                }
                else
                {
                    //Insert INTO Loaiduocpham table
                    sqlPattern = "insert into Loaiduocpham(Tenloaiduocpham)" +
                               " values ('" + obj.TenLoaiDuocPham + "')";
                }
                cmd.CommandText = sqlPattern;
                cmd.ExecuteNonQuery();
                string strSql = "Select @@IDENTITY";
                cmd.CommandText = strSql;
                cmd.Transaction = transaction;
                obj.Id = (int)cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            
        }

        private static bool CheckAlready(string name, out int id)
        {
            id = 0;
            OleDbConnection con = MyConnection.ConnectionData();
            string sql = "select ID from Loaiduocpham where Tenloaiduocpham='" + name + "'";
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            if (dataTable.Rows.Count > 0)
            {
                id = Convert.ToInt32(dataTable.Rows[0]["ID"].ToString());
            }
            return id > 0;
        }

        public void Delete(int id)
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "Delete From Loaiduocpham Where ID='"+id+"'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public LoaiDuocPhamDto Select(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "select * from Loaiduocpham where ID='"+id+"'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            LoaiDuocPhamDto ldp = new LoaiDuocPhamDto();
            while (dr.Read())
            {
                
                ldp.Id=Convert.ToInt32(dr["ID"].ToString());
                ldp.TenLoaiDuocPham=dr["Tenloaiduocpham"].ToString();
            }
            dr.Close();
            cn.Close();
            return ldp;
        }

        public LoaiDuocPhamDto[] SelectAll()
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "select * from Loaiduocpham ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            List <LoaiDuocPhamDto> ldpdto = new List <LoaiDuocPhamDto>();
            while (dr.Read())
            {
                LoaiDuocPhamDto ldp = new LoaiDuocPhamDto();
                ldp.Id = Convert.ToInt32(dr["ID"].ToString());
                ldp.TenLoaiDuocPham = dr["Tenloaiduocpham"].ToString();
                ldpdto.Add(ldp);
            }
            dr.Close();
            cn.Close();
            return ldpdto.ToArray();
        }

        #endregion
    }
}
